package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.LocalEntity;
import br.com.skytef.entity.ProductMerchandiseEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class LocalDao {
	public Connection connection;

	public LocalDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public LocalDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public LocalDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}
	/*-------------------------------------------------------------------------
	 * 
	 -------------------------------------------------------------------------*/
	public LocalEntity selectLocal(int id) throws Exception {

		List<LocalEntity> lstLocations = new ArrayList<LocalEntity>();
		Connection connection = new ConnectionFactory().getConnection();
		connection.setAutoCommit(true);

		try {
			LocalEntity local = new LocalEntity();
			
			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectLocal());			
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()==true) {

				local.setIdLocal(rs.getInt("idLocal"));
				local.setLabel(rs.getString("Identificador"));
				local.setDescription(rs.getString("Descricao"));
				local.setAvailable(rs.getBoolean("Disponivel"));

			}
			
			rs.close();
			stmt.close();
			connection.close();
			return local;

		} catch (SQLException e) {
			connection.close();
			throw new RuntimeException(e);
		}
	}
	

	public List<LocalEntity> selectLocalAvaliable() throws Exception {

		List<LocalEntity> lstLocations = new ArrayList<LocalEntity>();
		connection = new ConnectionFactory().getConnection();

		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectLocalAvaliable());			
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {

				LocalEntity local = new LocalEntity();

				local.setIdLocal(rs.getInt("idLocal"));
				local.setLabel(rs.getString("Identificador"));
				local.setDescription(rs.getString("Descricao"));
				local.setAvailable(rs.getBoolean("Disponivel"));

				lstLocations.add(local);
			}
			
			rs.close();
			stmt.close();
			return lstLocations;

		} catch (SQLException e) {
			connection.close();
			throw new RuntimeException(e);
		}
	}
	
	public boolean updateLocal(LocalEntity local) throws Exception {		
		connection = new ConnectionFactory().getConnection();
		boolean status = false;
		
		try {			
			PreparedStatement stmt = connection.prepareStatement(this.getQryUpdateLocal());
				   			
			stmt.setString(1, local.getLabel());
			stmt.setString(2, local.getDescription());	             			        
			stmt.setBoolean(3, local.isAvailable());
			stmt.setInt(4, local.getIdLocal());
			
			stmt.execute();
			status = true;
			stmt.close();
		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}
		return status;
	}

	/*
	 * Query
	 */

	private String getQryInsertLocal() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("INSERT INTO locais ");
		sql.append("            (idlocal, ");
		sql.append("             identificador, ");
		sql.append("             descricao, ");
		sql.append("             disponivel) ");
		sql.append("VALUES      (?, ?, ?); ");
		return sql.toString();
	}

	private String getQrySelectLocal() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT locais.idlocal, ");
		sql.append("       locais.identificador, ");
		sql.append("       locais.descricao, ");
		sql.append("       locais.disponivel ");
		sql.append("FROM   locais ");
		sql.append("WHERE  locais.idlocal = ?;");
		return sql.toString();
	}
	
	private String getQrySelectLocalAvaliable() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT locais.idlocal, ");
		sql.append("       locais.identificador, ");
		sql.append("       locais.descricao, ");
		sql.append("       locais.disponivel ");
		sql.append("FROM   locais ");
		sql.append("WHERE  locais.disponivel = true;");
		return sql.toString();
	}

	private String getQryUpdateLocal() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("UPDATE locais ");
		sql.append("SET    identificador = ?, ");
		sql.append("       descricao = ?, ");
		sql.append("       disponivel = ? ");
		sql.append("WHERE  idlocal = ?; ");
		return sql.toString();
	}
	
	private String getQryUpdateLocalStatus() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("UPDATE locais ");
		sql.append("SET    Disponivel = ?, ");
		sql.append("WHERE  idlocal = ?; ");
		return sql.toString();
	}

	private String getQryInsertLocalItem() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("INSERT INTO pedidos_has_locais ");
		sql.append("            (pedidos_idpedido, ");
		sql.append("             locais_idlocal, ");
		sql.append("             usuarios_idusuario, ");
		sql.append("             hora_abertura, ");
		sql.append("             hora_fechamento) ");
		sql.append("VALUES     (?, ?, ?, ?, ?); ");
		return sql.toString();
	}
	
	private String getQrySelectLocalItem() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT loc.idlocal, ");
		sql.append("       loc.identificador, ");
		sql.append("       loc.descricao, ");
		sql.append("       loc.disponivel, ");
		sql.append("       ped_loc.pedidos_idpedido, ");
		sql.append("       ped_loc.locais_idlocal, ");
		sql.append("       ped_loc.usuarios_idusuario, ");
		sql.append("       ped_loc.hora_abertura, ");
		sql.append("       ped_loc.hora_fechamento ");
		sql.append("FROM   navs_vend_dev.locais loc ");
		sql.append("       INNER JOIN pedidos_has_locais ped_loc ");
		sql.append("               ON loc.idlocal = ped_loc.locais_idlocal ");
		sql.append(";");
		return sql.toString();
	}
}
